package com.windfone.dao.impl.jdbc.mysql;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.windfone.common.Constant;
import com.windfone.dao.UserDao;
import com.windfone.model.User;
public class UserDaoImpl implements UserDao  { 
	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
		this.jdbcTemplate = jdbcTemplate;
	}

	String SQL_DELETE_USER=" DELETE FROM  user WHERE id=?";
	public int  deleteUser(final int keyValue) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				SQL_DELETE_USER,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String SQL_UPDATE_USER=" UPDATE user SET name=? ,password=? ,level=?   WHERE id=?";

	public int updateUser(final User  user) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				SQL_UPDATE_USER,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,user.getName());
						ps.setString(i++,user.getPassword());
						ps.setInt(i++,user.getLevel());
						ps.setInt(i++,user.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String SQL_INSERT_USER=" INSERT INTO user (name,password,level ) VALUES( ?,?,? )  ";

	public int insertUser(final User user) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				SQL_INSERT_USER,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,user.getName());
						ps.setString(i++,user.getPassword());
						ps.setInt(i++,user.getLevel());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String SQL_GET_USER_BYID=" SELECT * FROM  user  WHERE id=?";
	public   List<User>  getUserById(final int keyValue) {
		final List<User> result = new ArrayList<User>();
		try {
			jdbcTemplate.query(
				SQL_GET_USER_BYID,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
 							User to= new User();
 							to.setId(rs.getInt("id"));
 							to.setName(rs.getString("name"));
 							to.setPassword(rs.getString("password"));
 							to.setLevel(rs.getInt("level"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	String SQL_GET_ALL_USER=" SELECT * FROM  user  ORDER BY id ";
	public List<User> getAllUser( final int beginRecord) {
		final List<User> result = new ArrayList<User>();
		String sql = SQL_GET_ALL_USER + " LIMIT " + beginRecord + ", "+  (beginRecord+Constant.PAGE_SIZE);
		try {
			 jdbcTemplate.query(
				sql,
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 							User to= new User();
 							to.setId(rs.getInt("id"));
 							to.setName(rs.getString("name"));
 							to.setPassword(rs.getString("password"));
 							to.setLevel(rs.getInt("level"));
 							result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	String SQL_GET_ACOUNT_USER=" SELECT COUNT(1) FROM  user ";
	public int getAllRecorderNumber(  ) {
		final List<Integer> result = new ArrayList<Integer>();
		try {
			jdbcTemplate.query(
					SQL_GET_ACOUNT_USER,
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							result.add( rs.getInt(1));
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0 )
			return (result.get(0)).intValue();
		else 
			return 0;
	}
	
	String CHECK_ADMIN_USER_SQL = " SELECT * FROM user WHERE name=? AND password=?";
	public boolean checkAdminUser(final String user,final  String password) {
		final List<User> result = new ArrayList<User>();
		try {
			jdbcTemplate.query(
					CHECK_ADMIN_USER_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setString (1, user);
						ps.setString (2, password);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
 							User to= new User();
 							to.setId(rs.getInt("id"));
 							to.setName(rs.getString("name"));
 							to.setPassword(rs.getString("password"));
 							to.setLevel(rs.getInt("level"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0)
			return true;
		else 
			return false;
	}
	
	String CHECK_EXIST_USER_SQL = " SELECT * FROM user WHERE name=? ";
	public boolean checkExistUser(final String user) {
		final List<User> result = new ArrayList<User>();
		try {
			jdbcTemplate.query(
					CHECK_EXIST_USER_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setString (1, user);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
 							User to= new User();
 							to.setId(rs.getInt("id"));
 							to.setName(rs.getString("name"));
 							to.setPassword(rs.getString("password"));
 							to.setLevel(rs.getInt("level"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0)
			return true;
		else 
			return false;
	}
	String GET_USER_BY_NAME_SQL = " SELECT * FROM user WHERE name=? ";
	public User getUserByName(final String user) {
		final List<User> result = new ArrayList<User>();
		try {
			jdbcTemplate.query(
					CHECK_EXIST_USER_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setString (1, user);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							User to= new User();
							to.setId(rs.getInt("id"));
							to.setName(rs.getString("name"));
							to.setPassword(rs.getString("password"));
							to.setLevel(rs.getInt("level"));
							result.add(to);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0)
			return result.get(0);
		else 
			return null;
	}

	
}
